package com.cn.wanxi.dao.impl;

import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 封装数据
 * 数据来源于数据库
 * 访问数据库
 */
public class UserImpl implements UserDao {
    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` " +
                "(`username`, `password`, `phone`, `sex`, " +
                "`birthday`, `hobby`, `update_time`, `create_time`, " +
                "`enable`, `email`) VALUES (" +
                "'" + userModel.getUsername() + "'," +
                " '" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "'," +
                " '" + userModel.getSex() + "', " +
                "'" + (userModel.getBirthday().equals("") ? "now()" : userModel.getBirthday()) + "'," +
                " '" + userModel.getHobby() + "', " +
                "now()," +
                "now()," +
                " '0'," +
                " '" + userModel.getEmail() + "');\n";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findByUsername(String username) {
        String sql = "select * from user where username='" + username + "'";
        return getList(sql).get(0);
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where " +
                "username='" + userModel.getUsername() + "' " +
                "and password='" + userModel.getPassword() + "'";
        return getList(sql).get(0);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user where   true ";
        if (!"".equals(userModel.getUsername())) {
            sql += " and  username like '%" + userModel.getUsername() + "%' ";
        }
        if (userModel.getPhone() != null && !"".equals(userModel.getPhone())) {
            sql += " and phone like '%" + userModel.getPhone() + "%' ";
        }
        if (userModel.getSex() != null && !"全部".equals(userModel.getSex())) {
            sql += " and sex = '" + userModel.getSex() + "' ";
        }
//        足球，篮球
        if (userModel.getHobby() != null && !"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql += " and find_in_set('" + hobbys[i] + "',hobby)";
            }
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday  between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
        sql += " order by id desc";
        sql += " limit  " + (userModel.getPageModel().getCurPage() + -1) * userModel.getPageModel().getPageSize() + "," + userModel.getPageModel().getPageSize() + "";
//        limit  (curPage-1)*pageSize,pageSize
        return getList(sql);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select * from user where id=" + userModel.getId();

        return getList(sql).get(0);
    }

    @Override
    public Integer update(UserModel userModel) {
        String sql = "update user set " +
                " username='" + userModel.getUsername() + "', " +
                " phone='" + userModel.getPhone() + "'," +
                " sex='" + userModel.getSex() + "'," +
                " hobby='" + userModel.getHobby() + "'" +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public UserModel findByUsernameAndNotSelf(UserModel userModel) {
        String sql = "select * from user where username ='" + userModel.getUsername() + "'" +
                " and id!=" + userModel.getId();
        return getList(sql).get(0);
    }

    @Override
    public Integer del(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user where   true ";
        if (!"".equals(userModel.getUsername())) {
            sql += " and  username like '%" + userModel.getUsername() + "%' ";
        }
        if (userModel.getPhone() != null && !"".equals(userModel.getPhone())) {
            sql += " and phone like '%" + userModel.getPhone() + "%' ";
        }
        if (userModel.getSex() != null && !"全部".equals(userModel.getSex())) {
            sql += " and sex = '" + userModel.getSex() + "' ";
        }
//        足球，篮球
        if (userModel.getHobby() != null && !"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql += " and find_in_set('" + hobbys[i] + "',hobby)";
            }
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += " and birthday  between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
        sql += " order by id desc";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private List<UserModel> getList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setSex(resultSet.getString("sex"));
                model.setPhone(resultSet.getString("phone"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEnable(resultSet.getInt("enable"));
                model.setBirthday(resultSet.getString("birthday"));
                list.add(model);
            }
        } catch (Exception e) {

        }
        if (list.size() == 0) {
            list.add(null);
        }
        return list;
    }
}
